﻿<?xml version="1.0" encoding="utf-8" ?>
<commands>
  <user>
    <GetUsers>
      <![CDATA[
        SELECT 
          *
        FROM 
          [USER]
        WHERE
              ([ID] = @ID OR @ID = -1)
          AND ([LOGIN] = like '%'+@LOGIN+'%' or @LOGIN is null)
      ]]>
    </GetUsers>
    <CreateUser>
      <![CDATA[
        INSERT INTO [USER](
           [LOGIN]
          ,[PASSWORD]
          ,[FULLNAME]
          ,[EMAIL]
          ,[ENABLED]
          ,[TELEPHONE]
          ,[IM]
          ,[DESCRIPTION]
          ,[DEFAULT_ROLE_ID]
          ,[VELOCITY]
        )VALUES(
           @LOGIN
          ,@PASSWORD
          ,@FULLNAME
          ,@EMAIL
          ,@ENABLED
          ,@TELEPHONE
          ,@IM
          ,@DESCRIPTION
          ,@DEFAULT_ROLE_ID
          ,@VELOCITY
        )
      ]]>
    </CreateUser>
    <UpdateUser>
      <![CDATA[
        UPDATE [USER] SET
            [LOGIN] = @login,
            [FULLNAME] = @fullname,
            [EMAIL] = @email,
            [DEFAULT_ROLE_ID] = @default_role_ID,
            [TELEPHONE] = @telephone,
            [IM] = @IM,
            [VELOCITY] = @velocity,
            [DESCRIPTION] = @description,
            [ENABLED] = @enabled
        WHERE [ID] = @userID
        ]]>
    </UpdateUser>
    <GetLastInsertedUserID>
      <![CDATA[ SELECT IDENT_CURRENT('USER') ]]>
    </GetLastInsertedUserID>
    <GetUser>
      <![CDATA[
              SELECT [USER].*
              FROM [USER] 
              WHERE [LOGIN]=@UserName
              ]]>
    </GetUser>
    <CheckUserExist>
      <![CDATA[
              SELECT COUNT(*)
              FROM [User]
              WHERE [login]=@UserName
              ]]>
    </CheckUserExist>
    <GetUserByLogin>
      <![CDATA[
              SELECT *
              FROM [User]
              WHERE [login] = @login
              ]]>
    </GetUserByLogin>
    <GetUserByID>
      <![CDATA[
      SELECT [ID]
      ,[LOGIN]
      ,[PASSWORD]
      ,[FULLNAME]
      ,[EMAIL]
      ,[ENABLED]
      ,[TELEPHONE]
      ,[IM]
      ,[DESCRIPTION]
      ,[DEFAULT_ROLE_ID]
      ,[VELOCITY]
      FROM  [USER]
      WHERE [ID] = @userID
              ]]>
    </GetUserByID>
    <SetPassword>
      <![CDATA[
              UPDATE [User]
              SET [password]= @password
              WHERE [ID] = @userID
              ]]>
    </SetPassword>
    <SetFullName>
      <![CDATA[
              UPDATE [User]
              SET [fullname]= @fullname
              WHERE [login] = @login
              ]]>
    </SetFullName>
    <lockUser>
      <![CDATA[
              UPDATE [User]
              SET [enabled] = 0
              WHERE [login] = @login AND [enabled] = 1
              ]]>
    </lockUser>
    <unlockUser>
      <![CDATA[
              UPDATE [User]
              SET [enabled] = 1
              WHERE [login] = @login AND [enabled] = 0
              ]]>
    </unlockUser>
    <GetAdminByID>
      <![CDATA[
        SELECT count(u.[ID])
        FROM [USER] u JOIN [ROLE] r ON u.[DEFAULT_ROLE_ID] = r.[ID]
        WHERE u.[ID] = @userID AND
              r.[ISADMIN] = 1
        ]]>
    </GetAdminByID>
    <GetUserProfileID>
      <![CDATA[
        SELECT [ID]
        FROM [USER]
        WHERE [DEFAULT_ROLE_ID] = @roleID AND
              [IS_PROFILE] = 1
        ]]>
    </GetUserProfileID>
  </user>
  <role>
    <GetAllRolesAsBO>
      <![CDATA[
              SELECT 
                [ID], 
                [NAME]
              FROM 
                [Role]
              WHERE
                [TEAM] = 1 OR @teamRolesOnly = 0
              ]]>
    </GetAllRolesAsBO>
    <GetRoleByID>
      <![CDATA[
              SELECT [ID], 
                     [NAME]
              FROM [Role]
              WHERE [ID]=@roleID
              ]]>
    </GetRoleByID>
    <GetRoleForUser>
      <![CDATA[
              SELECT [Role].[ID], 
                     [Role].[NAME] 
              FROM [ROLE] INNER JOIN [USER] ON [USER].[DEFAULT_ROLE_ID] = [ROLE].[ID]
              WHERE [USER].[ID]=@userID
              ]]>
    </GetRoleForUser>
  </role>
</commands>
